﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using System.Collections;
using Concret;

namespace ORM
{
	/// <summary>
	/// 所有数据表类的基类
	/// </summary>
	public abstract class DataObject : IEquatable<DataObject>
	{
		internal DbOperation dbOp;

		/// <summary>
		/// 记录所有变化过的字段-值，ChangedFVs将在insert/update时使用，FVs将在select/count/page时使用
		/// </summary>
		internal Dictionary<string, object> ChangedFVs = new Dictionary<string, object>(), FVs = new Dictionary<string, object>();

		/// <summary>
		/// 数据表
		/// </summary>
		public abstract string TABLE_NAME { get; }
		/// <summary>
		/// 数据表注释
		/// </summary>
		public abstract string TABLE_COMMENT { get; }

		/// <summary>
		/// 主键
		/// </summary>
		public const string FIELD_ID = "id";
		/// <summary>
		/// 主键值。如果为0就是内存构造出来(或克隆出来)的对象
		/// </summary>
		public long Id { get; protected set; }

		#region 特殊方法
		/// <summary>
		/// 刷新this数据，从数据库中拿最新值
		/// </summary>
		/// <returns>this.Id对应的记录仍存在则返回最新值，否则将this.Id清零</returns>
		public void Refresh()
		{
			var result = GetById(Id);
			if (result == null)
				Id = 0;
			else
			{
				GetType().GetFields()
						.Where(f => (f.Name.EndsWith("_id") || f.Name.EndsWith("_val")) && !f.FieldType.IsSubclassOf(typeof(DataObject)))       //只拿数据库值，外键引用对象只拿id，不解析
						.ToList().ForEach(f => f.SetValue(this, f.GetValue(result)));

				FVs.Clear();
				ChangedFVs.Clear();
			}
		}

		/// <summary>
		/// 数据库字段-值的关系
		/// </summary>
		public Dictionary<string, object> FieldValues
		{
			get
			{
				var result = new Dictionary<string, object>();
				var type = GetType();
				var fields = type.GetFields();

				foreach (var dbField in fields.Where(f => f.Name.StartsWith("FIELD_")))
				{
					var varField = type.GetField("VAR_" + dbField.Name[6..]);
					var property = type.GetProperty(varField.GetValue(this).ToString());
					var value = property.GetValue(this, null);
					result[dbField.GetValue(this).ToString()] = value is DataObject ? (value as DataObject).Id : value;
				}

				return result;
			}
		}

		/// <summary>
		/// 根据dataTable中的数据填充具体的对象。具体对象有各自不同的字段，所以放在具体类中实现
		/// </summary>
		/// <param name="dataTable">查询了数据的dataTable</param>
		/// <returns>对象集合</returns>
		internal IEnumerable<DataObject> fillConcret(DataTable dataTable)
		{
			var result = new List<DataObject>();
			var type = GetType();
			foreach (DataRow row in dataTable.Rows)
			{
				var record = MemberwiseClone() as DataObject;
				record.Id = (long)row[FIELD_ID];
				record.FVs = new Dictionary<string, object>();
				record.ChangedFVs = new Dictionary<string, object>();

				type.GetFields(BindingFlags.Instance | BindingFlags.NonPublic)
				.Where(f => (f.Name.EndsWith("_id") || f.Name.EndsWith("_val")) && !f.FieldType.IsSubclassOf(typeof(DataObject))                       //只拿数据库值，外键引用对象只拿id，不解析
						&& new Func<string, bool>(col => row.Table.Columns.Contains(col) && row[col] != DBNull.Value)(f.Name.Remove(f.Name.Length - (f.Name.EndsWith("_id") ? 3 : 4)))      //datarow存在此列
					  )
				.ToList().ForEach(f => f.SetValue(record, row[f.Name.Remove(f.Name.Length - (f.Name.EndsWith("_id") ? 3 : 4))]));

				result.Add(record as DataObject);
			}

			return result;
		}

		public static bool operator ==(DataObject obj1, DataObject obj2) => obj1?.Id == obj2?.Id;       //基于NewId的全局唯一性，这里可以用id来判断是否同一表的同一记录
		public static bool operator !=(DataObject obj1, DataObject obj2) => !(obj1 == obj2);
		public bool Equals(DataObject compared) => this == compared;        //令IList.Contains可以直接支持DataObject的检索
		public override bool Equals(object compared) => this == compared as DataObject;
		public override string ToString() => base.ToString();
		public override int GetHashCode() => (int)Id;						//令HashSet/Dictionary之类的键值可以直接支持DataObject的检索
		#endregion

		#region 单表查询
		/// <summary>
		/// 根据this已填充或刷新的属性来查询数据(使用参数查询机制)。为了简化设计与兼顾灵活性，所有条件关系为单层单连接，如果要混合组合和优先级，则从结果中用linq Where过滤或选择GetBySqlWhere
		/// </summary>
		/// <param name="and_or">and/or</param>
		/// <param name="maxCount">结果最多记录数(默认无限制)</param>
		/// <param name="orders">结果排序设置</param>
		/// <returns>对象集合</returns>
		public IEnumerable<DataObject> GetByFVs(string and_or = "and", int maxCount = 0, params (string field, string order)[] orders)
		{
			var conditions = FVs.Select(kvp => (kvp.Key, kvp.Value == null ? "is" : "=", kvp.Value)).ToArray();
			return GetByParameter(conditions, and_or, maxCount, orders);
		}

		/// <summary>
		/// 查询(不使用参数查询机制)
		/// </summary>
		/// <param name="sqlWhere">查询条件where子句</param>
		/// <param name="maxCount">结果最多记录数(默认无限制)</param>
		/// <param name="orders">结果排序设置</param>
		/// <returns>对象集合</returns>
		public IEnumerable<DataObject> GetBySqlWhere(string sqlWhere = null, int maxCount = 0, params (string field, string order)[] orders)
		{
			var selected = "*";
			if (maxCount > 0) selected = $"top {maxCount} *";

			var order = "";
			if (orders.count() > 0) order = "order by " + orders.Select(fd => "{0} {1}".Fmt(dbOp.DBCode(fd.field), fd.order)).JoinBy(",");

			if (!string.IsNullOrEmpty(sqlWhere)) sqlWhere = "where " + sqlWhere;

			string sql = $"select {selected} from {dbOp.DBCode(TABLE_NAME)} {sqlWhere} {order}";
			var table = dbOp.TableByDataParameter(sql);
			var result = fillConcret(table);
			return result;
		}

		/// <summary>
		/// 查询(使用参数查询机制)
		/// </summary>
		/// <param name="conditions">查询条件，支持简单值，也支持is (not) null和(not) in 集合。为了简化设计与兼顾灵活性，所有条件关系为单层单连接，如果要混合组合和优先级，则从结果中用linq Where过滤或选择GetBySqlWhere</param>
		/// <param name="and_or">and/or</param>
		/// <param name="maxCount">结果最多记录数(默认无限制)</param>
		/// <param name="orders">结果排序设置</param>
		/// <returns>对象集合</returns>
		public IEnumerable<DataObject> GetByParameter(IEnumerable<(string field, string op, object value)> conditions = null, string and_or = "and", int maxCount = 0, params (string field, string order)[] orders)
		{
			var selected = "*";
			if (maxCount > 0) selected = $"top {maxCount} *";

			var order = "";
			if (orders.count() > 0) order = "order by " + orders.Select(fd => $"{dbOp.DBCode(fd.field)} {fd.order}").JoinBy(",");

			string sqlWhere = "";
			Dictionary<string, object> parameters = null;
			if (conditions.count() > 0)       //将conditions里的每个字段关系转成相应的sql字符串，以及对应的参数
			{
				sqlWhere = sqlWhereAndParams(conditions, and_or, out parameters);
				sqlWhere = "where " + sqlWhere;
			}

			string sql = $"select {selected} from {dbOp.DBCode(TABLE_NAME)} {sqlWhere} {order}";
			var table = dbOp.TableByKV(sql, parameters);
			var result = fillConcret(table);
			return result;
		}

		/// <summary>
		/// 查询(使用参数查询机制，与重载的GetByParameter一致)
		/// </summary>
		/// <param name="fieldVals">查询条件，支持简单值，也支持is (not) null和(not) in 集合。为了简化设计与兼顾灵活性，所有条件关系为单层单连接，如果要混合组合和优先级，则从结果中用linq Where过滤或选择GetBySqlWhere</param>
		/// <returns>对象集合</returns>
		public IEnumerable<DataObject> GetByParameter(params (string field, string op, object value)[] fieldVals)
		=> GetByParameter(conditions: fieldVals);

		/// <summary>
		/// 根据Id来获取记录
		/// </summary>
		/// <param name="Id">id值</param>
		/// <returns>存在返回对应那条记录，否则为null</returns>
		public DataObject GetById(long Id)
		=> GetByParameter((FIELD_ID, "=", Id)).FirstOrDefault();
		#endregion

		#region 获取结果记录数(一般用于分页)
		/// <summary>
		/// 根据this已填充或刷新的属性来获取结果记录数(使用参数查询机制)。为了简化设计与兼顾灵活性，所有条件关系为单层单连接，如果要混合组合和优先级，则选择CountBySqlWhere
		/// </summary>
		/// <param name="and_or">and/or</param>
		/// <returns>记录数</returns>
		public int CountByFVs(string and_or = "and")
		{
			var conditions = FVs.Select(kvp => (kvp.Key, kvp.Value == null ? "is" : "=", kvp.Value)).ToArray();
			return CountByParameter(and_or, conditions);
		}

		/// <summary>
		/// 获取结果记录数(不使用参数查询机制)
		/// </summary>
		/// <param name="sqlWhere">查询条件where子句</param>
		/// <returns>记录数</returns>
		public int CountBySqlWhere(string sqlWhere = null)
		{
			if (!string.IsNullOrEmpty(sqlWhere)) sqlWhere = "where " + sqlWhere;

			string sql = $"select count({dbOp.DBCode(FIELD_ID)}) from {dbOp.DBCode(TABLE_NAME)} {sqlWhere}";
			return (int)dbOp.ExecuteScalarByDataParameter(sql);
		}

		/// <summary>
		/// 获取结果记录数(使用参数查询机制)
		/// </summary>
		/// <param name="and_or">and/or</param>
		/// <param name="conditions">查询条件，支持简单值，也支持is (not) null和(not) in。为了简化设计与兼顾灵活性，所有条件关系为单层单连接，如果要混合组合和优先级，则选择CountBySqlWhere</param>
		/// <returns>记录数</returns>
		public int CountByParameter(string and_or = "and", params (string field, string op, object value)[] conditions)
		{
			string sqlWhere = "";
			Dictionary<string, object> parameters = null;
			if (conditions.count() > 0)       //将conditions里的每个字段关系转成相应的sql字符串，以及对应的参数
			{
				sqlWhere = sqlWhereAndParams(conditions, and_or, out parameters);
				sqlWhere = "where " + sqlWhere;
			}

			string sql = $"select count({dbOp.DBCode(FIELD_ID)}) from {dbOp.DBCode(TABLE_NAME)} {sqlWhere}";
			return (int)dbOp.ExecuteScalarByKV(sql, parameters);
		}

		/// <summary>
		/// 获取结果记录数(使用参数查询机制，与重载的CountByParameter一致)
		/// </summary>
		/// <param name="fieldVals">查询条件，支持简单值，也支持is (not) null和(not) in。为了简化设计与兼顾灵活性，所有条件关系为单层单连接，如果要混合组合和优先级，则选择CountBySqlWhere</param>
		/// <returns>记录数</returns>
		public int CountByParameter(params (string field, string op, object value)[] fieldVals)
		=> CountByParameter(conditions: fieldVals);
		#endregion

		#region 获取分页记录
		/// <summary>
		/// 根据this已填充或刷新的属性来获取分页数据(使用参数查询机制)。为了简化设计与兼顾灵活性，所有条件关系为单层单连接，如果要混合组合和优先级，则选择PageBySqlWhere
		/// </summary>
		/// <param name="indexBasedOne">页码(首页为1)</param>
		/// <param name="perPage">每页记录数</param>
		/// <param name="and_or">and/or</param>
		/// <param name="orders">排序设置(默认id降序)</param>
		/// <returns>对象集合</returns>
		public IEnumerable<DataObject> PageByFVs(int indexBasedOne = 1, int perPage = 10, string and_or = "and", params (string field, string order)[] orders)
		{
			var conditions = FVs.Select(kvp => (kvp.Key, kvp.Value == null ? "is" : "=", kvp.Value)).ToArray();
			return PageByParameter(indexBasedOne, perPage, conditions, and_or, orders);
		}

		/// <summary>
		/// 分页(不使用参数查询机制)
		/// </summary>
		/// <param name="indexBasedOne">页码(首页为1)</param>
		/// <param name="perPage">每页记录数</param>
		/// <param name="sqlWhere">查询条件where子句</param>
		/// <param name="orders">排序设置(默认id降序)</param>
		/// <returns>对象集合</returns>
		public IEnumerable<DataObject> PageBySqlWhere(int indexBasedOne = 1, int perPage = 10, string sqlWhere = null, params (string field, string order)[] orders)
		=> dbOp.PageBySqlWhere(this, indexBasedOne, perPage, sqlWhere, orders);

		/// <summary>
		/// 分页(使用参数查询机制)
		/// </summary>
		/// <param name="indexBasedOne">页码(首页为1)</param>
		/// <param name="perPage">每页记录数</param>
		/// <param name="conditions">查询条件，支持简单值，也支持is (not) null和(not) in。为了简化设计与兼顾灵活性，所有条件关系为单层单连接，如果要混合组合和优先级，则选择PageBySqlWhere</param>
		/// <param name="and_or"></param>
		/// <param name="orders">排序设置(默认id降序)</param>
		/// <returns>对象集合</returns>
		public IEnumerable<DataObject> PageByParameter(int indexBasedOne = 1, int perPage = 10, IEnumerable<(string field, string op, object value)> conditions = null, string and_or = "and", params (string field, string order)[] orders)
		=> dbOp.PageByParameter(this, indexBasedOne, perPage, conditions, and_or, orders);

		/// <summary>
		/// 分页(使用参数查询机制，与重载的PageByParameter一致)
		/// </summary>
		/// <param name="indexBasedOne">页码(首页为1)</param>
		/// <param name="fieldVals">查询条件，支持简单值，也支持is (not) null和(not) in。为了简化设计与兼顾灵活性，所有条件关系为单层单连接，如果要混合组合和优先级，则选择PageBySqlWhere</param>
		/// <returns>对象集合</returns>
		public IEnumerable<DataObject> PageByParameter(int indexBasedOne = 1, params (string field, string op, object value)[] fieldVals)
		=> PageByParameter(indexBasedOne, conditions: fieldVals);
		#endregion

		#region 插入
		/// <summary>
		/// 按ChangedFVs插入this记录
		/// </summary>
		/// <returns>成功返回新记录Id，否则返回null</returns>
		public long? Insert()
		{
			try
			{
				var fs = ChangedFVs.Keys;
				var fields = fs.Select(field => dbOp.DBCode(field)).ToList();
				var paramNames = fs.Select(field => "@" + field).ToList();

				fields.Add(dbOp.DBCode(FIELD_ID));
				paramNames.Add("@" + FIELD_ID);

				var sql = $"insert into {dbOp.DBCode(TABLE_NAME)} ({fields.JoinBy(",")}) values({paramNames.JoinBy(",")})";

				var parameters = ChangedFVs.ToDictionary(kv => kv.Key, kv => kv.Value ?? DBNull.Value);
				parameters[FIELD_ID] = Id = Helper.NewId;           //把this变成持久化对象
				if (dbOp.ExecuteNonQueryByKV(sql, parameters) == 0)
					return null;
				else
				{
					FVs.Clear();
					ChangedFVs.Clear();
					return Id;
				}
			}
			catch
			{
				return null;
			}
		}

		/// <summary>
		/// 批量插入
		/// </summary>
		/// <param name="records">全部记录除id外的字段-值映射</param>
		/// <param name="transaction">所属事务(默认自动事务)</param>
		/// <returns>是否全部成功(因为自动事务的强制性，所以要不全部成功，要不全部回滚)</returns>
		public bool Insert(params Dictionary<string, object>[] records)
		{
			if (records.count() < 1) return true;       //没有记录也成功

			var final = dbOp.Transaction == null;
			dbOp.BeginTransaction();

			try
			{
				foreach (var record in records)
				{
					var fs = record.Keys;
					var fields = fs.Select(field => dbOp.DBCode(field)).ToList();
					var paramNames = fs.Select(field => "@" + field).ToList();

					fields.Add(dbOp.DBCode(FIELD_ID));
					paramNames.Add("@" + FIELD_ID);

					var sql = $"insert into {dbOp.DBCode(TABLE_NAME)} ({fields.JoinBy(",")}) values({paramNames.JoinBy(",")})";

					var parameters = record.ToDictionary(kv => kv.Key, kv => kv.Value ?? DBNull.Value);
					parameters[FIELD_ID] = Helper.NewId;
					if (dbOp.ExecuteNonQueryByKV(sql, parameters) == 0) throw new Exception();
				}

				if (final) dbOp.Commit();
				return true;
			}
			catch
			{
				dbOp.Rollback();
				return false;
			}
		}
		#endregion

		#region 更新，返回执行成功与否(不返回影响的记录数是因为如果没任何改变也算成功)
		/// <summary>
		/// 按ChangedFVs更新this到数据库，返回执行成功与否(不返回影响的记录数是因为如果没任何改变也算成功)
		/// </summary>
		/// <returns>返回执行成功与否(不返回影响的记录数是因为如果没任何改变也算成功)</returns>
		public bool Update()
		{
			if (ChangedFVs.Count == 0) return true;

			try
			{
				var updates = ChangedFVs.Keys.Select(field => $"{dbOp.DBCode(field)}=@{field}").ToArray();
				var sql = $"update {dbOp.DBCode(TABLE_NAME)} set {updates.JoinBy(",")} where {dbOp.DBCode(FIELD_ID)}=@{FIELD_ID}";

				var parameters = ChangedFVs.ToDictionary(kv => kv.Key, kv => kv.Value ?? DBNull.Value);
				parameters[FIELD_ID] = Id;

				dbOp.ExecuteNonQueryByKV(sql, parameters);
				return true;
			}
			catch
			{
				return false;
			}
			finally
			{
				ChangedFVs.Clear();
				FVs.Clear();
			}
		}

		/// <summary>
		/// 批量更新，返回执行成功与否(不返回影响的记录数是因为如果没任何改变也算成功)
		/// </summary>
		/// <param name="records">待修改的记录的id-字段-值映射</param>
		/// <returns>返回执行成功与否(即使没改变任何记录也算成功)</returns>
		public bool Update(Dictionary<long, Dictionary<string, object>> records)
		{
			var final = dbOp.Transaction == null;
			dbOp.BeginTransaction();

			try
			{
				foreach (var record in records)
				{
					var updates = record.Value.Keys.Select(field => $"{dbOp.DBCode(field)}=@{field}").ToArray();
					var sql = $"update {dbOp.DBCode(TABLE_NAME)} set {updates.JoinBy(",")} where {dbOp.DBCode(FIELD_ID)}=@{FIELD_ID}";

					var parameters = record.Value.ToDictionary(kv => kv.Key, kv => kv.Value ?? DBNull.Value);
					parameters[FIELD_ID] = record.Key;

					dbOp.ExecuteNonQueryByKV(sql, parameters);
				}

				if (final) dbOp.Commit();
				return true;
			}
			catch
			{
				dbOp.Rollback();
				return false;
			}
		}
		#endregion

		#region 删除，级联时仅支持解除(即解除一级子表相关外键)，返回执行成功与否(不返回影响的记录数是因为如果记录本来就不存在也算成功)，若不成功则带上异常说明
		/// <summary>
		/// 删除this.Id对应的数据库记录，返回执行成功与否(如果记录本来就不存在也算成功)
		/// </summary>
		/// <param name="detach">是否级联解除</param>
		/// <returns>返回执行成功与否(如果记录本来就不存在也算成功)，若不成功则带上异常说明</returns>
		public (bool ok, string err) Delete(bool detach = false)
		{
			var res = DeleteByParameter(detach, "and", (FIELD_ID, "=", Id));
			if (res.ok) Id = 0;     //把this变成非持久化对象
			return res;
		}

		/// <summary>
		/// 批量删除，返回执行成功与否(如果记录本来就不存在也算成功)
		/// </summary>
		/// <param name="detach">是否级联解除</param>
		/// <param name="sqlWhere">条件where子句</param>
		/// <returns>返回执行成功与否(如果记录本来就不存在也算成功)，若不成功则带上异常说明</returns>
		public (bool ok, string err) DeleteBySqlWhere(bool detach = false, string sqlWhere = "")
		{
			var final = dbOp.Transaction == null;
			dbOp.BeginTransaction();

			string tableName = dbOp.DBCode(TABLE_NAME), fieldId = dbOp.DBCode(FIELD_ID);
			try
			{
				if (!string.IsNullOrEmpty(sqlWhere)) sqlWhere = "where " + sqlWhere;
				var count = dbOp.ExecuteScalarByKV($"select count({fieldId}) from {tableName} {sqlWhere}");
				if ((int)count > 0)
				{
					//处理每个子表
					var children = dbOp.ChildrenTableAndFKs(TABLE_NAME);
					foreach (KeyValuePair<string, List<string>> table_fks in children)
						foreach (var fk in table_fks.Value)
						{
							var where = $"{dbOp.DBCode(fk)} in (select {fieldId} from {tableName} {sqlWhere})";
							if (detach)     //如果解除模式，则解除子表
							{
								string sql = $"update {dbOp.DBCode(table_fks.Key)} set {dbOp.DBCode(fk)}=null where {where}";
								dbOp.ExecuteNonQueryByDataParameter(sql);
							}
							else            //如果不解除，若有子表记录引用，则回滚
							{
								string sql = $"select count({fieldId}) from {dbOp.DBCode(table_fks.Key)} where {where}";
								if (!0.Equals(dbOp.ExecuteScalarByKV(sql))) throw new Exception($"非级联解除模式，但{table_fks.Key}表有引用数据");
							}
						}

					//删除本表记录
					dbOp.ExecuteNonQueryByKV($"delete from {tableName} {sqlWhere}");
				}

				if (final) dbOp.Commit();
				return (true, null);
			}
			catch (Exception e)
			{
				dbOp.Rollback();
				return (false, e.Message);
			}
		}

		/// <summary>
		/// 批量删除，返回执行成功与否(如果记录本来就不存在也算成功)
		/// </summary>
		/// <param name="detach">是否级联解除</param>
		/// <param name="and_or">and/or</param>
		/// <param name="conditions">删除条件，支持简单值，也支持is (not) null和(not) in。为了简化设计与兼顾灵活性，所有条件关系为单层单连接，如果要混合组合和优先级，则选择DeleteBySqlWhere</param>
		/// <returns>返回执行成功与否(如果记录本来就不存在也算成功)，若不成功则带上异常说明</returns>
		public (bool ok, string err) DeleteByParameter(bool detach = false, string and_or = "and", params (string field, string op, object value)[] conditions)
		{
			var final = dbOp.Transaction == null;
			dbOp.BeginTransaction();

			try
			{
				string sqlWhere = null, tableName = dbOp.DBCode(TABLE_NAME), fieldId = dbOp.DBCode(FIELD_ID);
				Dictionary<string, object> parameters = null;
				if (conditions.count() > 0)       //将conditions里的每个字段关系转成相应的sql字符串，以及对应的参数
				{
					sqlWhere = sqlWhereAndParams(conditions, and_or, out parameters);
					sqlWhere = "where " + sqlWhere;
				}

				var count = dbOp.ExecuteScalarByKV($"select count({fieldId}) from {tableName} {sqlWhere}", parameters);
				if ((int)count > 0)
				{
					//处理每个子表
					var children = dbOp.ChildrenTableAndFKs(TABLE_NAME);
					foreach (KeyValuePair<string, List<string>> table_fks in children)
						foreach (var fk in table_fks.Value)
						{
							var where = $"{dbOp.DBCode(fk)} in (select {fieldId} from {tableName} {sqlWhere})";
							if (detach)     //如果解除模式，则解除子表
							{
								string sql = $"update {dbOp.DBCode(table_fks.Key)} set {dbOp.DBCode(fk)}=null where {where}";
								dbOp.ExecuteNonQueryByKV(sql, parameters);
							}
							else            //如果不解除，若有子表记录引用，则回滚
							{
								string sql = $"select count({fieldId}) from {dbOp.DBCode(table_fks.Key)} where {where}";
								if (!0.Equals(dbOp.ExecuteScalarByKV(sql, parameters))) throw new Exception($"非级联解除模式，但{table_fks.Key}表有引用数据");
							}
						}

					//删除本表记录
					dbOp.ExecuteNonQueryByKV($"delete from {tableName} {sqlWhere}", parameters);
				}

				if (final) dbOp.Commit();
				return (true, null);
			}
			catch (Exception e)
			{
				dbOp.Rollback();
				return (false, e.Message);
			}
		}

		/// <summary>
		/// 批量删除，返回执行成功与否(如果记录本来就不存在也算成功，与重载的DeleteByParameter一致)
		/// </summary>
		/// <param name="fieldVals">删除条件，支持简单值，也支持is (not) null和(not) in。为了简化设计与兼顾灵活性，所有条件关系为单层单连接，如果要混合组合和优先级，则选择DeleteBySqlWhere</param>
		/// <returns>返回执行成功与否(如果记录本来就不存在也算成功)，若不成功则带上异常说明</returns>
		public (bool ok, string err) DeleteByParameter(params (string field, string op, object value)[] fieldVals)
		=> DeleteByParameter(conditions: fieldVals);
		#endregion

		/// <summary>
		/// 根据条件生成查询参数和相应的where子句
		/// </summary>
		/// <param name="conditions">条件集合，支持简单值，也支持is (not) null和(not) in</param>
		/// <param name="and_or">各条件连接方式</param>
		/// <param name="parameters">返回查询参数</param>
		/// <returns></returns>
		internal string sqlWhereAndParams(IEnumerable<(string field, string op, object value)> conditions, string and_or, out Dictionary<string, object> parameters)
		{
			//参数名称要加索引后缀是因为conditions里面会重复的字段名(针对同一字段的多个条件)
			var conds = conditions.Select((fov, i) =>
			{
				if (fov.value == null)      //is null或is not null，不能用参数
					return "{0} {1} null".Fmt(dbOp.DBCode(fov.field), fov.op == "=" ? "is" : fov.op == "<>" || fov.op == "!=" ? "is not" : fov.op);

				if (fov.value is Array)                                                             //数组就针对in/not in来展开
				{
					var arr = fov.value as Array;
					if (arr.Length == 0) return "1=2";

					var vals = arr.Cast<object>().Where(val => val != null);
					if (vals.Count() > 0)
						return "{0} {1} ({2})".Fmt(dbOp.DBCode(fov.field), fov.op, vals.Select((_, m) => $"@{fov.field}_{i}_{m}").JoinBy(","));
					else
						return "1=2";
				}

				return "{0} {1} @{2}_{3}".Fmt(dbOp.DBCode(fov.field), fov.op, fov.field, i);      //具体数值的处理，使用参数
			});

			var cons = new List<(string p, object v)>();
			int j = 0;
			foreach (var fov in conditions)
			{
				if (fov.value is IEnumerable && !(fov.value is string))
				{
					var arr = fov.value as IEnumerable;
					int k = 0;
					foreach (var val in arr)
						if (val is DataObject)            //本身就非空
							cons.Add((p: $"{fov.field}_{j}_{k++}", v: (val as DataObject).Id));
						else if (val != null)
							cons.Add((p: $"{fov.field}_{j}_{k++}", v: val));
				}
				else if (fov.value != null)
					cons.Add((p: $"{fov.field}_{j}", v: fov.value));

				j++;
			}

			parameters = cons.ToDictionary(pv => pv.p, pv => pv.v);
			return conds.JoinBy($" {and_or} ");

			/*
			 (not) in 集合 这里的【集合】支持混杂组合，比如
			 DbOp.Project.GetByParameter((Project.FIELD_ID, ">", 44924409802129408),
										 (Project.FIELD_MANAGER, "not in", new List<long>()),	//自动处理集合无数据的情形(sql将转换成1=2)
										 (Project.FIELD_PARENT_PROJECT, "in", new object[]
																				{ 43448950023258112, 45086203367653376, 47527650683518976,
																				  null,			//自动处理in (null)的情形(sql转换时跳过)
																				  new Project(DbOp) { Name_Val = "*洽谈项目" }.GetByFVs().FirstOrDefault()
																				}
										 ));
			以上转换成sql将为：where id>44924409802129408 and 1=2 and parent_project in (43448950023258112,45086203367653376,47527650683518976, 【*洽谈项目的id，若无记录就忽略】)
			 */
		}
	}
}